from datetime import timedelta
import datetime
import sqlite3

import concurrent
from constant import Constants
from service.das.stock_day_das import StockDateDayDas
from service.das.stock_monitor_das import StockDas
from .das.database_pool import DatabasePool
from .das.tushare_pool import getDayStock, getRealTime


# 初始化监控系统表结构
class DBInit:
    def checkAndInitDB(self):
        table_name = "ant_monitor_stock"
        # 检查表是否存在
        if not self.check_table_exists(table_name):
            print(f"表 '{table_name}' 不存在，正在创建...")
            self.init_db()
        else:
            print(f"表 '{table_name}' 已存在。")

    def check_table_exists(self, table_name):
        """检查指定表是否存在"""
        db_pool = DatabasePool(max_connections=1)
        cursor = db_pool.connect().cursor()
        cursor.execute(
            f"""
            SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';
        """
        )
        table_exists = cursor.fetchone() is not None
        cursor.close()
        return table_exists

    # 初始化所有的SQL

    def init_db(self):
        db_pool = DatabasePool(max_connections=1)
        conn = db_pool.connect()
        cursor = conn.cursor()
        try:
            # 读取 SQL 文件
            sql_file = "./resources/base.sql"
            with open(sql_file, "r", encoding="utf-8") as f:
                sql_script = f.read()

            # 分割 SQL 语句并执行
            for statement in sql_script.split(";"):
                statement = statement.strip()
                if statement:  # 确保不是空语句
                    cursor.execute(statement)

            # 提交事务
            conn.commit()
            print("所有 SQL 语句执行成功！")

        except sqlite3.Error as e:
            print(f"执行 SQL 时发生错误: {e}")

        finally:
            # 关闭游标和连接
            cursor.close()
            conn.close()


class RefreshStockInit:
    def refreshAllStockInfo(self):

        stockDas = StockDas()
        stockList = stockDas.get_stocks_code()

        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures = {
                executor.submit(self.process_stock, stock_code): stock_code
                for stock_code in stockList
            }

            for future in concurrent.futures.as_completed(futures):
                stock_code = futures[future]
                try:
                    future.result()
                except Exception as e:
                    print(f"Error in thread for stock {stock_code}: {e}")

    def process_stock(stock_code):
        """
        查询最后交易日期并调用 getDayStock 方法处理
        """
        try:
            stockDas = StockDas()
            last_trade_date = stockDas.get_last_trade_date(stock_code)
            if not last_trade_date:
                print(f"No trade data found for {stock_code}")
                return

            # 计算查询日期范围
            start_time = (last_trade_date + timedelta(days=1)).strftime("%Y-%m-%d")
            end_time = datetime.now().strftime("%Y-%m-%d")

            # 调用 getDayStock 查询数据
            stock_data = getDayStock(stock_code, start_time, end_time)
            StockDateDayDas.add_stock(stock_data)
        except Exception as e:
            print(f"Error processing stock {stock_code}: {e}")
